-- This stored procedure assigns the identity field in temporary mailing list
-- tables in the order that the mailing list table should be queried.
CREATE PROCEDURE [dbo].[asi_SetMailingListIdentityField]
   @tableName sysname,
   @fieldName sysname,
   @keyFieldName sysname,
   @orderByClause nvarchar(256)
AS
BEGIN
   DECLARE @sql nvarchar(800)

   CREATE TABLE #SetIdentityTable
   (
      SetIdentityTableKey uniqueidentifier,
      IntId int IDENTITY(1, 1)
   )
       
   SET @sql = 'INSERT #SetIdentityTable (SetIdentityTableKey) SELECT [' + @keyFieldName + '] FROM [' + @tableName + '] ' + @orderByClause
   EXEC(@sql)
    
   SET @sql = 'UPDATE [' + @tableName + '] SET [' + @fieldName + ']=#SetIdentityTable.IntId FROM #SetIdentityTable WHERE #SetIdentityTable.SetIdentityTableKey=[' + @tableName + '].[' + @keyFieldName + ']'
   EXEC(@sql)
END

GO